package com.datasecops.scannerservices.extractwatermark;

import com.datasecops.scannerservices.watermark.JNILinuxSo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.*;

/**postgersql数据库水印提取的相关操作
 * @author: GeXiaoLong
 * @time: 2023/2/14 19:54
 */
@Service
@Slf4j
public class PostGreSqlExtract {
    @Resource
    private ExtractWatermark extractWatermark;

    @Resource
    private JNILinuxSo jniLinuxSo;

    //postgersql数据库的连接
    public void postgresqlToCsv(String name) throws ClassNotFoundException, SQLException, IOException {
        //（1）使用Java JDBC连接数据库：

        Class.forName("org.postgresql.Driver");

        String url = "jdbc:postgresql://10.26.18.66:5432/text2";

        String user = "postgres";

        String password = "sc.9102";

        String DB = "text2";

        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }


        //（2）构建SQL查询语句，查询出所需要的数据：

        String model = "soul";
        String tableName = name ;
        String sql = "SELECT * FROM " + model + "." + "\""+tableName+"\"";
        //这个是表数据
        Statement stmt = conn.createStatement();

        //这个是表结构
        DatabaseMetaData metaData = conn.getMetaData();

        ResultSet pkInfo  = metaData.getPrimaryKeys(DB, model, tableName);

        System.out.println(pkInfo  == null);
        StringBuilder surface = new StringBuilder("");
        String primary = null;
        // System.out.println(pkInfo.next());
        while (pkInfo.next()){
            System.out.print("数据库名称:"+pkInfo .getString("TABLE_CAT")+"                  ");
            System.out.print("表名称:"+pkInfo .getString("TABLE_NAME")+"                  ");


            surface.append("CREATE TABLE");
            surface.append(" ");
            surface.append(pkInfo.getString("TABLE_NAME"));
            surface.append(" ");
            surface.append("(");
            surface.append("\n");


            System.out.print("主键列的名称:"+pkInfo .getString("COLUMN_NAME")+"                  ");
            primary = pkInfo .getString("COLUMN_NAME");
            System.out.print("类型:"+pkInfo .getString("PK_NAME")+"                  ");
            System.out.println("");
        }

        ResultSet tableInfo = metaData.getColumns(DB,model, tableName, "%");//这里的text后期要改成动态的
        while (tableInfo.next()){
            // 表的名字
            System.out.print("表名:"+tableInfo.getString("TABLE_NAME")+"    ");
            // 列的名称
            System.out.print("列名:"+tableInfo.getString("COLUMN_NAME")+"    ");

            // 默认值
            System.out.print("默认值 :"+tableInfo.getString("COLUMN_DEF")+"  ");
            // 字段的类型
            System.out.print("字段的类型:"+tableInfo.getString("TYPE_NAME")+"   ");

            // 是否可以为空
            System.out.print("是否可以为空:"+tableInfo.getString("IS_NULLABLE")+"  ");

            // 是否为自增
            System.out.print("是否为自增:"+tableInfo.getString("IS_AUTOINCREMENT")+" ");
            // 字段说明
            System.out.print("字段说明:"+tableInfo.getString("REMARKS")+"  ");
            // 长度(有时候是错的)
            System.out.print("长度:"+tableInfo.getString("COLUMN_SIZE")+" ");
            surface.append(tableInfo.getString("COLUMN_NAME"));
            surface.append(" ");
            if ("timestamp".equalsIgnoreCase(tableInfo.getString("TYPE_NAME"))){//这里是因为pg和mysql的时间戳类型不一样,暂时这样处理
                surface.append("datetime");
            }else{
                surface.append(tableInfo.getString("TYPE_NAME"));
            }
            //surface.append(tableInfo.getString("TYPE_NAME"));
            surface.append(" ");
            surface.append("(");
            if ("timestamp".equalsIgnoreCase(tableInfo.getString("TYPE_NAME"))){
                surface.append("6");
            }else {
                surface.append(tableInfo.getString("COLUMN_SIZE"));
            }
            //surface.append(tableInfo.getString("COLUMN_SIZE"));
            surface.append(")");
            surface.append(" ");
            surface.append("COLLATE utf8mb4_unicode_ci");
            surface.append(" ");
            if ("NO".equalsIgnoreCase(tableInfo.getString("IS_NULLABLE"))){
                surface.append("NOT NULL");
            }else{
                surface.append("DEFAULT NULL");
            }
            surface.append(" ");
            surface.append("COMMENT");
            surface.append(" ");
            surface.append("'");
            surface.append(tableInfo.getString("REMARKS"));
            surface.append("'");
            surface.append(",");
            surface.append("\n");
            System.out.println();
        }
        surface.append("PRIMARY KEY");
        surface.append(" ");
        surface.append("(");
        surface.append(primary);
        surface.append(")");
        surface.append(" ");
        surface.append("USING BTREE");
        surface.append("\n");
        surface.append(")");
        surface.append(" ");
        surface.append("ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        String string = surface.toString();
        string = string.replace("bytea","binary");
        string = string.replace("int2","tinyint");
        string = string.replace("int4","int");
        string = string.replace("int8","bigint");
        string = string.replace("numeric","decimal");
        string = string.replace("timestamp","datetime");
        System.out.println(string);

        //（3）将查询结果写入CSV文件：
        ResultSet rs = stmt.executeQuery(sql);
 /*       OutputStreamWriter fw = new OutputStreamWriter(new FileOutputStream("P:\\text\\your_file.csv"),
                StandardCharsets.UTF_8);//win版的*/
        OutputStreamWriter fw = new OutputStreamWriter(new FileOutputStream("/root/your_file.csv"),
                StandardCharsets.UTF_8);
//        FileWriter fw = new FileWriter("P:\\text\\your_file.csv");
        ResultSetMetaData metaData1 = rs.getMetaData();
        int columeCount = metaData1.getColumnCount();//
        //String columnName = metaData.getColumnName(1);
        //System.out.println(columeCount);
        String String = "";

        for (int i = 1; i <= columeCount; i++) {
            if (!(i == columeCount)) {
                String = String + metaData1.getColumnName(i) + ",";
            } else {
                String = String + metaData1.getColumnName(i) + "\n";
            }
        }
        fw.append(String);
        //String outputString = "";
        while (rs.next()) {
            String outputString = "";
            for (int i = 1; i <= columeCount; i++) {
                if (!(i == columeCount)) {
                    String str = rs.getString(i);
                    boolean b = rs.wasNull();
                    int i1 = -1;
                    if (str != null) {
                        i1 = str.indexOf("\n");//如果获取到的字符串中含有换位符\n ,那么i1的值就不是-1,只有不包含的时候才是-1;
                    }
                    if ( i1 != -1){
                        String replaces = null;
                        if (str != null){
                            replaces = str.replace("\n", "");//把自动生成的换位符去掉
                        }
                        outputString = outputString + replaces + ",";
                    }else {
                        if (b){
                            outputString = outputString  + "NULL"+",";
                        }else {
                            outputString = outputString + rs.getString(i) + ",";
                        }
                        //outputString = outputString + rs.getString(i) + ",";
                    }
                } else {
                    outputString = outputString + rs.getString(i) + "\n";
                }
            }
            //           String outputString = rs.getString(1) + "," + rs.getString(2) + "," + rs.getString(3) + "\n";

            fw.append(outputString);

        }

        fw.flush();

        fw.close();

        //（4）关闭数据库连接：

        rs.close();

        stmt.close();

        conn.close();

        importData(string,tableName);

    }

     /**
      *创建新表和导入csv
      * @Description
      * @author GeXiaoLong
      * @time 2023/2/15 14:33
      * @param
      * @return
      */
    public void importData(String surface, String tableName) throws SQLException {
        //        1. 使用JDBC驱动程序：


        String loadDataQuery = " LOAD DATA LOCAL INFILE '/root/your_file.csv' INTO TABLE " +
                tableName +" FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 LINES";//linux


/*        String loadDataQuery = " LOAD DATA LOCAL INFILE 'E://DB//your_files.csv' INTO TABLE " +
                tableName +" FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 LINES";*/
//
//        st.executeUpdate(loadDataQuery);
//        System.out.println(loadDataQuery);
//
//        PreparedStatement preparedStatement = con.prepareStatement(loadDataQuery);
//
//        preparedStatement.execute();

        log.error("数据库安装程序进来了吗?");
        Statement st = null;
        try {
            log.error("数据库安装程序运行进来了吗1?");
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://10.26.18.58:3306/mysql?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai";
            String username = "root";
            String password = "Wzx.1104";
            Connection c = DriverManager.getConnection(url, username, password);
            st = c.createStatement();
            st.executeUpdate("CREATE DATABASE IF NOT EXISTS WatermarkExtraction");//创建数据库
            st.executeUpdate("USE WatermarkExtraction");

            int i = st.executeUpdate(surface);//创建一张新表
            System.out.println(i);
            //rs = st.executeQuery("select * from student");
            System.out.println(loadDataQuery);

            //执行导入csv文件代码
            boolean execute = st.execute(loadDataQuery);
            int updateCount = st.getUpdateCount();
            while (!(updateCount > 0)){
                System.out.println("正在导入");
            }
            System.out.println(execute);//导入csv文件
//System.out.println(loadDataQuery);
            //st.executeUpdate( loadDataQuery);
            //st.execute(loadDataQuery);
        } catch (Exception e) {
            log.error("数据库添加程序运行失败,原因是: ",e);
        } finally {
            st.close();
        }

        log.error("加注水印的程序进来了吗1");

//        OracleDB oracleDB = new OracleDB();
//        oracleDB.watermarking();
    }
     /**
      *获取csv文件的行数
      * @Description
      * @author GeXiaoLong
      * @time 2023/2/15 9:53
      * @param
      * @return
      */
    public Integer ReadCSV() {
        int count = 0;
        try {
            FileReader fr = new FileReader("/root/your_file.csv");
            BufferedReader br = new BufferedReader(fr);
            //count = 0;
            while (br.readLine() != null) {
                count++;
            }
            System.out.println("行数：" + count);
            br.close();
            fr.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return count;
    }

     /**
      *数据库导入的行数
      * @Description
      * @author GeXiaoLong
      * @time 2023/2/15 9:54
      * @param
      * @return
      */
    public Integer DBRows(String name) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://10.26.18.58:3306/mysql?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai";
        String username = "root";
        String password = "Wzx.1104";
        String tableName = name;
        String mode = "WatermarkExtraction";


        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Statement stmt = conn.createStatement();

        String sql = "SELECT COUNT(*) FROM " +mode +"."+ tableName;



        ResultSet rs = stmt.executeQuery(sql);
        int count = 0;
        if (rs.next()) {
            count = rs.getInt(1);
        }
        return count;
    }


    /**
      *postgersql数据库水印提取
      * @Description
      * @author GeXiaoLong
      * @time 2023/2/15 9:43
      * @param
      * @return
      */
    public Integer postGerSqlWatermarkInformationExtraction(String tableName) throws SQLException, IOException,
            ClassNotFoundException {
        extractWatermark.text(tableName);
        postgresqlToCsv(tableName);
        Integer integer = ReadCSV();//csv文件获取到的行数
        Integer integer1 = DBRows(tableName);//数据库导入的行数
        Integer text = null;
        while ((integer - 1) == integer1) {
            text = jniLinuxSo.text();
            break;
        }
        return text;
    }

    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        PostGreSqlExtract postGreSqlExtract = new PostGreSqlExtract();
        //postGreSqlExtract.postgresqlToCsv();
    }

}

